<?php
    ## Defenitivo
$Trn162 = "SELECT
					CONCAT(FraOrigen0,'-',LPAD(FraReferenciaAg,6,0)) AS ORInternal,
					date_format(FraFecha, '%m/%d/%Y') AS FraFecha,
					date_format(FraFecha, '%Y%m%d') AS FraFecha2,
					date_format(FraFecha, '%Y-%m-%d') AS FraFecha3,
					date_format(FraFecha, '%h:%i:%s') AS FraHora,
					FraReferenciaAg,
					FraReferenciaCo,
					FraComisionCorr,
					FraTasa,
					FraMensaje,
					FraStatus,
					BnfCedula,
					BnfNombre,
					BnfApellidos,
					BnfTelefono,
					BnfBarrio,
					BnfBarrio AS BNFNOTES,
					BnfDireccion,
					BnfProvincia,
					BnfTelefono,
					BnfTelefono2,
					ciudades.CiuCiudad AS BnfCiudad,
					hd_paises.pai_NombreUSD AS BnfPais,
					hd_paises.pai_Sufijo AS BnfPaisCode,
					hd_paises.pai_CodeIso AS BnfPaisISONum,
					bp_IdBanco,
					cb_Banco,
					cb_Sucursal,
					cb_Cuenta,
					cb_Cuenta_prx,
					cb_Cuenta_sux,
					cb_Cuenta_mod,
					cb_TipoCuenta,
					CorrNumero,
					CorrAgente,
					hf_actypes.SHORT_NAME AS TipoCuenta,
					CliNombre,
					CliApellidos,
					CliDNI,
					CliDomicilio,
					CliCP,
					CliTelefono,
					cl_country.pai_NombreESP AS CliPais,
					cl_country.pai_Sufijo AS CliPaisCode,
					cl_country.pai_CodeIso AS CliPaisISONum,
					cl_city.CiuCiudad AS CliCiudad,
					ROUND(FraEnviado) AS FraEnviado,
					ROUND(FraDolares) AS FraDolares,
					ROUND(FraARecibir,0) AS FraARecibir,
					CobMon.miso_ID AS FraMonCobro,
					PagMon.miso_ID AS FraMonDPago,
					bp_Name,
					tcn_Short AS FraModo,
					OFCorrCode AS FraAgenciaPago,
					mp_short AS FraModoPago
				FROM facturas IGNORE INDEX (IdxCli)
				INNER JOIN clientes ON CliNumero = FraCliNumero
				INNER JOIN beneficiarios ON BnfNumero = FraBnfNumero
				INNER JOIN corresponsales ON CorrNumero = FraCorresponsal
				INNER JOIN hd_transactionconcept ON tcn_ID = FraModo
				LEFT OUTER JOIN ciudades ON CiuNumero = BnfCiudad
				LEFT OUTER JOIN ciudades AS cl_city		ON cl_city.CiuNumero = CliCiudad
				INNER JOIN hd_paises ON pai_CodElite = BnfPais
				INNER JOIN hd_paises AS cl_country	ON cl_country.pai_CodElite = ClPais
				INNER JOIN hd_monedasiso AS CobMon ON CobMon.miso_cod_elite = FraMonCobro
				INNER JOIN hd_monedasiso AS PagMon ON PagMon.miso_cod_elite = FraMonDPago
				INNER JOIN hd_modospago ON mp_ID = FraModoPago
				LEFT OUTER JOIN beneficiarioscuentas ON cb_Id = FraCtaBenef
				LEFT OUTER JOIN hf_actypes ON hf_actypes.REF = cb_TipoCuenta
				LEFT OUTER JOIN hd_bancos ON bp_ID = cb_Banco
				LEFT OUTER JOIN oficcorresp ON OFId = FraAgenciaPago
				WHERE FraOrdenPago IN (#ID#)
				AND FraOrdenTrans IS NULL
				AND FraStatus = 3
				AND FraCorresponsal = #Corr#
				AND FraModo = 1";

    $Trn170 = "SELECT
					FraIciDate,
					FraIncReferenciaCo,
					FraIncVersion,
					FraIciNotas,
					FraStatus,
					Trs_CorrTrans,
                                        FraIciCorrReferenceIci
				FROM facturas IGNORE INDEX (IdxCli)
				INNER JOIN facturasextjoins ON FraID = FraExtID
				INNER JOIN facturas_income ON FraID = FraIncID
				INNER JOIN transmitions ON Trs_ID = FraIncOrdenTrans
                                LEFT JOIN facturas_incidencias ON FraIciID = FraID
				WHERE FraIciDate > '#LastDate#'
                                AND FraStatus = 18
				AND FraIncCorresponsal = #Corr#
				AND FraModo = 2";

    $Trn178 = "SELECT
					FraFecha,
					FraIncReferenciaCo,
					FraIncVersion,
					FraIncNotes,
					FraStatus,
					Trs_CorrTrans
				FROM facturas IGNORE INDEX (IdxCli)
				INNER JOIN facturasextjoins ON FraID = FraExtID
				INNER JOIN facturas_income ON FraID = FraIncID
				INNER JOIN transmitions ON Trs_ID = FraIncOrdenTrans
				WHERE FraFecha > '#LastDate#'
                                AND (FraNumero > 0 AND FraStatus IN (8,9,11)
                                    OR FraStatus = 1)
				AND FraIncCorresponsal = #Corr#
				AND FraModo = 2";


	### Temporario para transiccao de processos

## Query Antigo
$QurOR = "SELECT
					CONCAT(FraOrigen0,'-',LPAD(FraReferenciaAg,6,0)) AS ORInternal,
					date_format(FraFecha, '%m/%d/%Y') AS FraFecha,
					date_format(FraFecha, '%h:%i:%s') AS FraHora,
					FraReferenciaCo,
					BnfCedula,
					BnfNombre,
					BnfTelefono,
					BnfBarrio AS BNFNOTES,
					BnfDireccion,
					CiuCiudad AS BnfCiudad,
					pai_NombreUSD AS BnfPais,
					bp_IdBanco,
					cb_Banco,
					cb_Sucursal,
					cb_Cuenta,
					cb_Cuenta_prx,
					cb_Cuenta_sux,
					cb_Cuenta_mod,
					cb_TipoCuenta,
					CorrNumero,
					hf_actypes.SHORT_NAME AS TipoCuenta,
					CliNombre,
					CliDNI,
					CliCiudad,
					ROUND(FraEnviado) AS FraEnviado,
					ROUND(FraARecibir,0) AS FraARecibir,
					'L' AS FraMonDPago,
					bp_Name,
					tcn_Short AS FraModo,
					mp_short AS FraModoPago
				FROM Facturas IGNORE INDEX (IdxCli)
				INNER JOIN ordenespago ON Op_Orden = FraOrdenPago
				INNER JOIN clientes ON CliNumero = FraCliNumero
				INNER JOIN beneficiarios ON BnfNumero = FraBnfNumero
				INNER JOIN corresponsales ON CorrNumero = Op_Corr
				LEFT OUTER JOIN ciudades ON CiuNumero = BnfCiudad
				LEFT OUTER JOIN beneficiarioscuentas ON cb_Id = FraCtaBenef
				LEFT OUTER JOIN hd_bancos ON bp_ID = cb_Banco
				INNER JOIN hd_paises ON pai_CodElite = BnfPais
				INNER JOIN hd_transactionconcept ON tcn_ID = FraModo
				LEFT OUTER JOIN hf_actypes ON hf_actypes.REF = cb_TipoCuenta
				INNER JOIN hd_modospago ON mp_ID = FraModoPago
				WHERE FraOrdenPago IN (#ID#)
				AND FraCorresponsal = #Corr#
				AND FraOrdenTrans IS NULL
				AND FraStatus = 3";

#Query Novo
$QurTrn = "SELECT
					CONCAT(FraOrigen0,'-',LPAD(FraReferenciaAg,6,0)) AS ORInternal,
					date_format(FraFecha, '%m/%d/%Y') AS FraFecha,
					date_format(FraFecha, '%Y%m%d') AS FraFecha2,
					date_format(FraFecha, '%Y-%m-%d') AS FraFecha3,
					date_format(FraFecha, '%h:%i:%s') AS FraHora,
					FraReferenciaAg,
					FraReferenciaCo,
					FraComisionCorr,
					FraTasa,
					FraMensaje,
					FraStatus,
					BnfCedula,
					BnfNombre,
					BnfApellidos,
					BnfTelefono,
					BnfBarrio,
					BnfBarrio AS BNFNOTES,
					BnfDireccion,
					BnfProvincia,
					BnfTelefono,
					BnfTelefono2,
					ciudades.CiuCiudad AS BnfCiudad,
					hd_paises.pai_NombreUSD AS BnfPais,
					hd_paises.pai_Sufijo AS BnfPaisCode,
					hd_paises.pai_CodeIso AS BnfPaisISONum,
					bp_IdBanco,
					cb_Banco,
					cb_Sucursal,
					cb_Cuenta,
					cb_Cuenta_prx,
					cb_Cuenta_sux,
					cb_Cuenta_mod,
					cb_TipoCuenta,
					CorrNumero,
					CorrAgente,
					hf_actypes.SHORT_NAME AS TipoCuenta,
					CliNombre,
					CliApellidos,
					CliDNI,
					CliDomicilio,
					CliCP,
					CliTelefono,
					cl_country.pai_NombreESP AS CliPais,
					cl_country.pai_Sufijo AS CliPaisCode,
					cl_country.pai_CodeIso AS CliPaisISONum,
					cl_city.CiuCiudad AS CliCiudad,
					ROUND(FraEnviado) AS FraEnviado,
					ROUND(FraDolares) AS FraDolares,
					ROUND(FraARecibir,0) AS FraARecibir,
					CobMon.miso_ID AS FraMonCobro,
					PagMon.miso_ID AS FraMonDPago,
					bp_Name,
					tcn_Short AS FraModo,
					OFCorrCode AS FraAgenciaPago,
					mp_short AS FraModoPago
				FROM facturas IGNORE INDEX (IdxCli)
				INNER JOIN clientes ON CliNumero = FraCliNumero
				INNER JOIN beneficiarios ON BnfNumero = FraBnfNumero
				INNER JOIN corresponsales ON CorrNumero = FraCorresponsal
				INNER JOIN hd_transactionconcept ON tcn_ID = FraModo
				LEFT OUTER JOIN ciudades ON CiuNumero = BnfCiudad
				LEFT OUTER JOIN ciudades AS cl_city		ON cl_city.CiuNumero = CliCiudad
				INNER JOIN hd_paises ON pai_CodElite = BnfPais
				INNER JOIN hd_paises AS cl_country	ON cl_country.pai_CodElite = ClPais
				INNER JOIN hd_monedasiso AS CobMon ON CobMon.miso_cod_elite = FraMonCobro
				INNER JOIN hd_monedasiso AS PagMon ON PagMon.miso_cod_elite = FraMonDPago
				INNER JOIN hd_modospago ON mp_ID = FraModoPago
				LEFT OUTER JOIN beneficiarioscuentas ON cb_Id = FraCtaBenef
				LEFT OUTER JOIN hf_actypes ON hf_actypes.REF = cb_TipoCuenta
				LEFT OUTER JOIN hd_bancos ON bp_ID = cb_Banco
				LEFT OUTER JOIN oficcorresp ON OFId = FraAgenciaPago
				WHERE FraOrdenPago IN (#ID#)
				AND FraOrdenTrans IS NULL
				AND FraStatus = 3
				AND FraCorresponsal = #Corr#
				AND FraModo = 1";
	# QurRegen
/*	$QurTrn1 = "SELECT
					CONCAT(FraOrigen0,'-',LPAD(FraReferenciaAg,6,0)) AS ORInternal,
					date_format(FraFecha, '%m/%d/%Y') AS FraFecha,
					date_format(FraFecha, '%Y%m%d') AS FraFecha2,
					date_format(FraFecha, '%Y-%m-%d') AS FraFecha3,
					date_format(FraFecha, '%h:%i:%s') AS FraHora,
					FraReferenciaAg,
					FraReferenciaCo,
					FraComisionCorr,
					FraTasa,
					FraMensaje,
					FraStatus,
					BnfCedula,
					BnfNombre,
					BnfTelefono,
					BnfBarrio,
					BnfDireccion,
					BnfProvincia,
					BnfTelefono,
					BnfTelefono2,
					ciudades.CiuCiudad AS BnfCiudad,
					hd_paises.pai_NombreUSD AS BnfPais,
					hd_paises.pai_Sufijo AS BnfPaisCode,
					hd_paises.pai_CodeIso AS BnfPaisISONum,
					bp_IdBanco,
					cb_Banco,
					cb_Sucursal,
					cb_Cuenta,
					cb_Cuenta_prx,
					cb_Cuenta_sux,
					cb_Cuenta_mod,
					cb_TipoCuenta,
					CorrNumero,
					CorrAgente,
					hf_actypes.SHORT_NAME AS TipoCuenta,
					CliNombre,
					CliDNI,
					CliDomicilio,
					CliCP,
					CliTelefono,
					cl_country.pai_NombreESP AS CliPais,
					cl_country.pai_Sufijo AS CliPaisCode,
					cl_country.pai_CodeIso AS CliPaisISONum,
					cl_city.CiuCiudad AS CliCiudad,
					ROUND(FraEnviado) AS FraEnviado,
					ROUND(FraARecibir,0) AS FraARecibir,
					CobMon.miso_ID AS FraMonCobro,
					PagMon.miso_ID AS FraMonDPago,
					bp_Name,
					tcn_Short AS FraModo,
					OFCorrCode AS FraAgenciaPago,
					mp_short AS FraModoPago
				FROM Facturas IGNORE INDEX (IdxCli)
				INNER JOIN clientes ON CliNumero = FraCliNumero
				INNER JOIN beneficiarios ON BnfNumero = FraBnfNumero
				INNER JOIN corresponsales ON CorrNumero = FraCorresponsal
				INNER JOIN hd_transactionconcept ON tcn_ID = FraModo
				LEFT OUTER JOIN ciudades ON CiuNumero = BnfCiudad
				LEFT OUTER JOIN ciudades AS cl_city		ON cl_city.CiuNumero = CliCiudad
				INNER JOIN hd_paises ON pai_CodElite = BnfPais
				INNER JOIN hd_paises AS cl_country	ON cl_country.pai_CodElite = ClPais
				INNER JOIN hd_monedasiso AS CobMon ON CobMon.miso_cod_elite = FraMonCobro
				INNER JOIN hd_monedasiso AS PagMon ON PagMon.miso_cod_elite = FraMonDPago
				INNER JOIN hd_modospago ON mp_ID = FraModoPago
				LEFT OUTER JOIN beneficiarioscuentas ON cb_Id = FraCtaBenef
				LEFT OUTER JOIN hf_actypes ON hf_actypes.REF = cb_TipoCuenta
				LEFT OUTER JOIN hd_bancos ON bp_ID = cb_Banco
				LEFT OUTER JOIN oficcorresp ON OFId = FraAgenciaPago
				WHERE FraOrdenTrans = {$TrnID}
				AND FraStatus = 7";
	# Query Regen			
	$QurTrn2 = "SELECT 
					CONCAT(FraOrigen0,'-',LPAD(FraReferenciaAg,6,0)) AS ORInternal,
					date_format(FraFecha, '%m/%d/%Y') AS FraFecha,
					date_format(FraFecha, '%Y%m%d') AS FraFecha2,
					date_format(FraFecha, '%h:%i:%s') AS FraHora,
					FraReferenciaCo,
					BnfCedula,
					BnfNombre,
					BnfTelefono,
					BnfBarrio AS BNFNOTES,
					BnfDireccion,
					CiuCiudad AS BnfCiudad,
					pai_NombreUSD AS BnfPais,
					bp_IdBanco,
					cb_Banco,
					cb_Sucursal,
					cb_Cuenta,
					cb_Cuenta_prx,
					cb_Cuenta_sux,
					cb_Cuenta_mod,
					cb_TipoCuenta,
					CorrNumero,
					hf_actypes.SHORT_NAME AS TipoCuenta,
					CliNombre,
					CliDNI,
					CliCiudad,
					ROUND(FraEnviado) AS FraEnviado,
					ROUND(FraARecibir,0) AS FraARecibir,
					'L' AS FraMonDPago,
					bp_Name,
					tcn_Short AS FraModo,
					mp_short AS FraModoPago
				FROM Facturas IGNORE INDEX (IdxCli)
				INNER JOIN clientes ON CliNumero = FraCliNumero
				INNER JOIN beneficiarios ON BnfNumero = FraBnfNumero
				INNER JOIN corresponsales ON CorrNumero = FraCorresponsal
				LEFT OUTER JOIN ciudades ON CiuNumero = BnfCiudad
				LEFT OUTER JOIN beneficiarioscuentas ON cb_Id = FraCtaBenef
				LEFT OUTER JOIN hd_bancos ON bp_ID = cb_Banco
				INNER JOIN hd_paises ON pai_CodElite = BnfPais
				INNER JOIN hd_transactionconcept ON tcn_ID = FraModo
				LEFT OUTER JOIN hf_actypes ON hf_actypes.REF = cb_TipoCuenta
				INNER JOIN hd_modospago ON mp_ID = FraModoPago
				WHERE FraOrdenTrans = {$TrnID}
				AND FraStatus = 7";*/